package de.softcon.mobileoffence.persistence;

import java.util.Queue;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Base class for all classes accessing the mobileOffence database.
 * 
 * @author fmannan
 *
 */
public class MobileOffenceDatabaseAdapter extends SQLiteOpenHelper 
{
    private static final String DATABASE_NAME = "owiMobile";
    private static final int DATABASE_VERSION = 1;
    
    /**
     * Column names, which are not table specific.
     */
    private static final String ID = "_id";
    private static final String START_DATE_TIME = "start-date-time";
    private static final String END_DATE_TIME = "end-date-time";
    private static final String VALVEPOSITIONS = "valve-positions";
    private static final String LICENCE_PLATE = "licence-plate";
    private static final String IS_VALID = "is-valid";
    private static final String LATITUDE = "latitude";
    private static final String LONGITUDE = "longitude";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";
    private static final String MANDATOR = "mandator";

    /**
     * Marking table - contains markings.
     */
    private static final String MARKING_TABLE_NAME = "markings";    
    private static final String MARKING_TABLE_CREATE =
        "create table " + MARKING_TABLE_NAME + " (" +
        ID + " integer primary key autoincrement, " +
        START_DATE_TIME + " text, " +
        END_DATE_TIME + " text, " +
        LICENCE_PLATE + " text, " +
        LATITUDE + " real, " +
        LONGITUDE + " real, " +
        VALVEPOSITIONS + " text);";

    /**
     * Incident table - contains incidents.
     */
    private static final String INCIDENT_TABLE_NAME = "incidents";    
    private static final String INCIDENT_TABLE_CREATE =
        "create table " + INCIDENT_TABLE_NAME + " (" +
        ID + " integer primary key autoincrement, " +
        START_DATE_TIME + " text, " +
        LICENCE_PLATE + " text, " +
        IS_VALID + " text, " +
        LATITUDE + " real, " +
        LONGITUDE + " real, " +
        VALVEPOSITIONS + " text);";

    /**
     * user table - contains users and their login credentials.
     */
    private static final String USER_TABLE_NAME = "users";    
    private static final String USER_TABLE_CREATE =
        "create table " + INCIDENT_TABLE_NAME + " (" +
        ID + " integer primary key autoincrement, " +
        MANDATOR + " real, " +
        USERNAME + " real, " +
        PASSWORD + " text);" +
        " primary key (" + USERNAME + ", " + MANDATOR + ", " + ID + ") );";

    
	public MobileOffenceDatabaseAdapter(Context context)
	{
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
	
	@Override
	public void onCreate(SQLiteDatabase database) {
		//Creates all tables which should be present in the database
		database.execSQL(MARKING_TABLE_CREATE);
		database.execSQL(INCIDENT_TABLE_CREATE);
		database.execSQL(USER_TABLE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	/**
	 * Adds a new user to the database.
	 */
	protected void AddUser(String username, String password)
	{
		SQLiteDatabase database = this.getWritableDatabase();
		ContentValues newEntry = new ContentValues();
		newEntry.put(USERNAME, username);
		newEntry.put(PASSWORD, password);
		database.insert(USER_TABLE_NAME, null, newEntry);
	}
	
	/**
	 * @return true if the login was successful, false otherwise.
	 */
	protected boolean Login(String username, String password)
	{
		SQLiteDatabase database = this.getReadableDatabase();
		Cursor result = database.query(USER_TABLE_NAME, new String []{USERNAME}, null, null, null, null, null);
		return result.getCount() > 0 ? true : false;
	}
}
